'''
Author: duliang thinktanker@163.com
Date: 2024-05-05
LastEditors: duliang thinktanker@163.com
LastEditTime: 2025-08-30 21:26:21
FilePath: 
Description: 实现数据查询转图片并返回
'''
# import ast
import os
import sqlite3
import time
import zlib
# import re
import numpy as np
import math
import pandas as pd
import locale
# import decimal
# from threading import Thread
# from concurrent.futures import ThreadPoolExecutor, as_completed
# from multiprocessing import Process
# from PIL import Image
# from multiprocessing import Pool
import matplotlib
from datetime import datetime
from txttopic import getnydhbpic
from savedatatodb import *

matplotlib.use('Agg')  # 设置matplotlib不显示图形，只保存图形
import matplotlib.pyplot as plt
# import matplotlib.ticker as ticker
# import matplotlib.dates as mdates
import shutil
from sklearn.linear_model import LinearRegression
import scipy.stats as stats
import requests
from Crypto.Cipher import AES
from base64 import b64decode
import json
from const import *
import checkdata

YOLOIMGPATH = r'D:\yoloimages'  # yolo图片存放路径
locale.setlocale(locale.LC_CTYPE, 'chinese')  # 设置为中文环境
plt.rcParams['font.sans-serif'] = ['SimHei']  # 无衬线字体，用于显示正文字体
plt.rcParams['axes.unicode_minus'] = False  # 解决负号'-'显示为方块的问题


def sendImgToYoloPath(filename: str, imgdata: bytes):
    """_summary_

    Args:
        filename (str): 保存文件路径
        imgdata (bytes): 保存文件内容
    """
    with open(filename, 'wb') as f:
        f.write(imgdata)
    with open('./data/miniindex/latest.jpg', 'wb') as f:
        f.write(imgdata)


def updateUser(openid, version=None):
    """_summary_

    Args:
        openid (_type_): _description_
    """
    conn = sqlite3.connect('./data/users.db')
    # 创建一个游标对象，用于执行SQL语句
    c = conn.cursor()
    c.execute("SELECT openid,count FROM users WHERE openid=?", (openid, ))
    result = c.fetchone()
    if result:
        count = result[1]
        count += 1
        # 执行SQL语句，更新访问时间，访问次数加1
        c.execute(
            "UPDATE users SET lasttime=?,count=?,version=? WHERE openid=?",
            (int(time.time()), count, version, openid))
        # 提交事务
        conn.commit()
    c.close()
    conn.close()


def editUserName(id, username):
    """
    更新用户的用户名
    :param id: 用户的ID
    :param username: 新的用户名
    """
    conn = sqlite3.connect('./data/users.db')
    c = conn.cursor()
    c.execute("UPDATE users SET username=? WHERE id=?", (username, id))
    conn.commit()
    c.close()
    conn.close()


def code2session(code, username="未知用户"):
    """
    使用code换取openid和session_key
    :param code: 小程序登录凭证code
    :return: 返回包含openid和session_key的字典，或None表示失败
    """
    url = f'https://api.weixin.qq.com/sns/jscode2session?appid={APP_ID}&secret={APP_SECRET}&js_code={code}&grant_type=authorization_code'
    realname = ''
    try:
        response = requests.get(url)
        result = response.json()

        if 'errcode' in result:
            print(f"Error getting session: {result['errmsg']}")
            return None
        openid = result['openid']
        session_key = result['session_key']
        allowed = 0
        # 创建一个SQLite数据库连接
        conn = sqlite3.connect('./data/users.db')

        # 创建一个游标对象，用于执行SQL语句
        c = conn.cursor()
        # 执行SQL语句，查询openid是否存在
        c.execute("SELECT openid FROM users WHERE openid=?", (openid, ))
        user = c.fetchone()

        if user is None:
            # 执行SQL语句，插入openid和session_key
            c.execute(
                "INSERT INTO users (openid, session_key,allowed,username,time) VALUES (?, ?,?,?,?)",
                (openid, session_key, allowed, username, int(time.time())))

            # 提交事务
            conn.commit()
        else:
            # 执行SQL语句，查询allowed是否为true
            c.execute("SELECT allowed,username FROM users WHERE openid=?",
                      (openid, ))
            allowed, realname = c.fetchone()
            print(allowed, realname)
            # if not allowed:
        c.close()
        conn.close()
        # 成功返回，包含openid和session_key
        return {
            'openid': openid,
            # 'session_key': session_key,
            'allowed': allowed,
            'realname': realname,
        }
    except Exception as e:
        print(f"An error occurred: {e}")
        return None


def decrypt_user_info(session_key, encrypted_data, iv):
    """解密微信小程序加密的用户信息"""
    session_key = b64decode(session_key)
    encrypted_data = b64decode(encrypted_data)
    iv = b64decode(iv)

    cipher = AES.new(session_key, AES.MODE_CBC, iv)
    decrypted = cipher.decrypt(encrypted_data)
    padding = decrypted[-1]
    if padding < 1 or padding > 16:
        return None
    decrypted = decrypted[:-padding]

    user_info_str = decrypted.decode('utf-8')
    return json.loads(user_info_str)


def get_prediction(time_list, data_list, plus_time, debug=False):
    result = []
    pre_list = []
    res = 0
    data = np.array(time_list).reshape(-1, 1)
    for i in range(0, len(data_list)):
        labels = np.array(data_list[i])
        # 使用线性回归模型
        model = LinearRegression()
        model.fit(data, labels)
        next_time = int(time.time()) + plus_time
        # 预测未来的值
        future_data = np.array([next_time]).reshape(-1, 1)  # 预测时间为next_time时的值
        prediction = model.predict(future_data)
        delta = prediction[0] - labels[0]
        if labels[0]:
            rate = delta / labels[0] * 100
        else:
            rate = 0
        if debug:
            print("预测值:", prediction[0])
            print("当前值:", labels[0])
            print('rate:', rate)
            print('delta:', delta)
        if rate:
            if abs(rate) > 1:
                if rate > 0:
                    # print("上涨")
                    res = 1
                else:
                    # print("下跌")
                    res = 2
            else:
                res = 0
        else:
            res = 3
        result.append(f'{res}@{round(prediction[0],2)}')
        pre_value = model.predict(data)
        start_value = round(pre_value.tolist()[0], 2)
        end_value = round(pre_value.tolist()[-1], 2)
        pre_list.append(end_value)
    return result, pre_list


def cox_stuart(list_c, debug=False):
    lst = list_c.copy()
    raw_len = len(lst)
    if raw_len % 2 == 1:
        del lst[int((raw_len - 1) / 2)]  # 删除中位数
    c = int(len(lst) / 2)
    n_pos = n_neg = 0
    for i in range(c):
        diff = lst[i + c] - lst[i]
        if diff > 0:
            n_pos += 1
        elif diff < 0:
            n_neg += 1
        else:
            continue
    num = n_pos + n_neg
    k = min(n_pos, n_neg)  #  双边检验
    p_value = 2 * stats.binom.cdf(k, num, 0.5)  #  二项分布
    if debug:
        print("k: ", k)
        print("num:", num)
        print('fall:%i, rise:%i, p-value:%f' % (n_neg, n_pos, p_value))
    if n_pos > n_neg and p_value < 0.05:  #  双边检验
        return 1  #'increasing'
    elif n_neg > n_pos and p_value < 0.05:  #  双边检验
        return 2  #'decreasing'
    else:
        return 0  #'no trend'


def trendline(data):  # 拟合曲线
    order = 1
    index = [i for i in range(1, len(data) + 1)]  # x轴坐标
    coeffs = np.polyfit(index, list(data), order)  # 曲线拟合
    # k = coeffs[0] # 斜率
    return coeffs


def judge_slope(coeffs, data, degree, shake=1):
    tan_k = math.tan(degree * math.pi / 180)  # 注意弧度转化
    if coeffs[0] >= tan_k:
        return 1  #"上升"
    elif coeffs[0] <= -tan_k:
        return 2  #"下降"
    else:
        return get_shake(coeffs, data, shake)


def get_shake(coeffs, data, shake):
    count = 0
    for i, d in enumerate(data):  # i+1相当于横坐标，从1开始
        y = np.polyval(coeffs, i + 1)
        count += (y - d)**2
    # print("count: ",count)
    if count > shake:
        return 3  #波动
    else:
        return 0  #平稳


def sendTextToWxMsg(target_wxid='filehelper', text='hello'):
    '''
    发送信息到微信
    :param target_wxid: 目标微信ID
    :param text: 发送的信息
    :return: 发送成功返回True，否则返回False
    '''
    data = {"wxid": target_wxid, "content": text}
    try:
        resp = requests.post(url='http://127.0.0.1:8080/api' + "/sendtxtmsg",
                             json=data)
        if resp.json()["code"] == 200:
            return True
        else:
            return False
    except Exception as e:
        print(e)
        return False


def copy_file(src, dst):
    """
    使用shutil.copy()复制文件
    
    :param src: 源文件路径
    :param dst: 目标文件或目录路径
    """
    try:
        shutil.copy(src, dst)
        print(f"\033[33m文件从 {src} 成功复制到 {dst}\033[0m")
    except FileNotFoundError:
        print("源文件不存在，请检查路径。")
    except Exception as e:
        print(f"复制文件时发生错误：{e}")


def decompress_string(data: bytes):
    '''
    解压缩数据
    '''
    # decompressed_data = bz2.decompress(data)
    decompressed_data = zlib.decompress(data)
    return decompressed_data.decode('utf-8')


def judge(recvtime, recvdata, station):
    '''
    用标识符"END"判断是追加还是保存信息
    '''
    # print('原始数据：',recvdata)
    try:
        if b'END' in recvdata:
            '''
            正常接收数据
            '''
            print("数据")
            recv_equip = recvdata[0:8]
            print(f"\033[33m{recv_equip}\033[0m")
            recvdata = recvdata[8:-3].replace(recv_equip, b'')
            recvdata = decompress_string(recvdata)
            print(f"\033[33m{recvdata}\033[0m")
            if station == 1:
                save_data1(recvdata, recvtime, station)
            elif station == 2:
                save_data2(recvdata, recvtime, station)
        elif b'FIL' in recvdata:  #FIL结尾
            '''
            获取文件、图片等，用于收集一站摄像头图片
            '''
            print("文件")
            data_length = len(recvdata[8:-3])

            # 按小时存储
            dir_name = time.strftime(r"%Y/%m/%d/%H",
                                     time.localtime(time.time()))
            filepath = f'./filedata/{dir_name}'
            if not os.path.exists(filepath):
                os.makedirs(filepath)
            with open('./filedata/recent.txt', 'w', encoding='utf8') as f:
                f.write(dir_name)
            # (H:\gitee\industrial-control-network\test.html)FIL
            # file_path = re.findall(rb"\((.*?\..*?)\)FIL", recvdata[-100:])
            file_path = re.findall(rb"\(\((.*?)\)\)FIL", recvdata[-100:])
            # print(file_path[0].decode('utf8'))
            if file_path:
                file_path = file_path[0]
                file_path = file_path.decode('utf8')
                # 形如x:/12221/2#/zk.jpg兼容以前格式
                # print(file_path)
                base_name = os.path.basename(file_path)
                tag_list = file_path.split('/')
                try:
                    file_length = tag_list[1]
                    file_id = tag_list[2]
                except Exception as e:
                    print(e)
                    file_length = 0
                    file_id = ''
                with open('./filedata/camera.txt', 'r', encoding='utf8') as f:
                    camera_list = f.read()
                if f'{file_id}' not in camera_list:
                    with open('./filedata/camera.txt', 'a',
                              encoding='utf8') as f:
                        f.write(f'{file_id}\n')

                # print(data_length, file_length)
                if int(data_length) > int(file_length):
                    file_name = os.path.splitext(base_name)[0]
                    file_extension = os.path.splitext(file_path)[1]
                    # recvdata = zlib.decompress(recvdata)
                    # zlib.compress()
                    recvdata = re.sub(rb"\(\((.*?)\)\)FIL", b'', recvdata)
                    file_time = int(time.time())
                    with open(
                            f'{filepath}/{file_time}_{file_name}_{file_id}{file_extension}',
                            'wb') as f:
                        # print(recvdata[10:])
                        # 后移8位 剔除 mac信息
                        f.write(recvdata[8:])
                    print(file_id)
                    # 清污机专用处理
                    if "清污机2" in file_id:
                        sendImgToYoloPath(
                            filename=
                            f"{YOLOIMGPATH}/{file_time}_{file_name}_{file_id}{file_extension}",
                            imgdata=recvdata[8:])
                    # print("文件保存成功")
                    conn_camera = sqlite3.connect(camera_path)
                    cur_camera = conn_camera.cursor()
                    sql_camera = "INSERT INTO camera1 (name,time) VALUES (?, ?)"
                    cur_camera.execute(sql_camera, (
                        file_id,
                        file_time,
                    ))
                    conn_camera.commit()
                    cur_camera.close()
                else:
                    print("文件不完整,放弃保存")
            else:
                print("无法识别文件")
        elif b'SIM' in recvdata:
            '''
            获取物联网sim卡数据
            '''
            print("sim卡")
            recvdata = (recvdata[:-3].replace(b'SIM', b'')).decode('utf8')
            print(f"\033[33m{recvdata}\033[0m")
            if station == 1:
                save_data1(recvdata, recvtime, station)
            elif station == 2:
                save_data2(recvdata, recvtime, station)
        else:
            '''
            都不满足，输出错误
            '''
            print("错误数据")
    except Exception as e:
        print(e)
        # copy_file('./image/com_error.png',
        #           f'./image/{station}_zhan_status.png')
        # with open('./data/check.json', 'r') as f:
        #     j = json.load(f)
        #     j[station] = 0
        # with open('./data/check.json', 'w') as f:
        #     json.dump(j, f)
        # with open(f'./data/errormsg{station}.txt', 'r') as f:
        #     txt = f.read()
        #     if not txt:
        #         if station == 1:
        #             sendTextToWxMsg(text=f"{station}站异常\n{e}\n{recvdata}")
        #         with open(f'./data/errormsg{station}.txt', 'w') as f:
        #             f.write(f"{station}站异常\n{e}\n{recvdata}")


def getsw_sw(name: str):
    conn1 = sqlite3.connect(swdb_path)
    cursor = conn1.cursor()
    # name = list(name)
    # name = "%".join(name)
    sqltxt = f'SELECT sysw,xysw,time FROM swdb WHERE name="{name}" ORDER BY time DESC'
    cursor.execute(sqltxt)
    temp = cursor.fetchone()
    conn1.close()
    return temp


def getgq_1zhan(past_time, plus_time):
    '''
    获取一站工情
    :param past_time: 过去时间 小时
    :param plus_time: 未来时间 分钟
    :return:返回给微信群用的字符串
    '''
    result = []
    # result_pre = []
    conn = sqlite3.connect(db1_path)
    c = conn.cursor()
    sqltxt = "SELECT time FROM swll ORDER BY time DESC LIMIT 1"
    rs = c.execute(sqltxt)
    rf = rs.fetchone()
    now_time = rf[0]
    result.append(
        time.strftime(r"%y年%m月%d日%H时%M分%S秒", time.localtime(now_time)) + "\n")
    start_time = now_time - past_time
    sqltxt = f"SELECT sysw,xysw,sqsw,time FROM swll WHERE TIME BETWEEN {start_time} AND {now_time} ORDER BY time DESC"
    rs = c.execute(sqltxt)
    rf = rs.fetchall()
    time_list = []
    for i in range(0, len(rf)):
        time_list.append(rf[i][-1])
    # if rf:
    res_list = []
    data_list = []
    for i in range(0, len(rf[0])):
        try:
            flat_numbers = [num[i] for num in rf]
            data_list.append(flat_numbers)
            # res = cox_stuart(flat_numbers, False)
            # coeffs = trendline(flat_numbers)
            # res = judge_slope(coeffs, flat_numbers, degree=0.01, shake=1)
            # res_list.append(res)
        except Exception as e:
            print(e)
    res_list, pre_list = get_prediction(time_list=time_list,
                                        data_list=data_list[:-1],
                                        plus_time=plus_time,
                                        debug=False)
    result.append(f'上游(m):{rf[0][0]}@{res_list[0]}@{pre_list[0]}')
    result.append(f'下游(m):{rf[0][1]}@{res_list[1]}@{pre_list[1]}')
    result.append(f'栅前(m):{rf[0][2]}@{res_list[2]}@{pre_list[2]}\n')
    sqltxt = f"SELECT mgyl,time FROM gongshui WHERE TIME BETWEEN {start_time} AND {now_time} ORDER BY time DESC"
    rs = c.execute(sqltxt)
    rf = rs.fetchall()
    # if rf:
    time_list = []
    for i in range(0, len(rf)):
        time_list.append(rf[i][-1])
    flat_numbers = [num[0] for num in rf]
    # coeffs = trendline(flat_numbers)
    # res = judge_slope(coeffs, flat_numbers, degree=0.01, shake=1)
    # res = cox_stuart(flat_numbers, False)
    res_list, pre_list = get_prediction(time_list=time_list,
                                        data_list=[flat_numbers],
                                        plus_time=plus_time,
                                        debug=False)
    result.append(f'母管压力(MPa):{rf[0][0]}@{res_list[0]}@{pre_list[0]}\n')
    # res_list = []

    for index, ename in enumerate(ename_list):
        data_list = []
        sqltxt = f"""SELECT Uab,Ubc,Uca,Ia,Ib,Ic,P,Q,cos,lcU,lcI,time FROM dianyadianliu WHERE ename="{ename}" AND TIME BETWEEN {start_time} AND {now_time} ORDER BY time DESC"""
        rs = c.execute(sqltxt)
        rf = rs.fetchall()
        if rf:
            # res_list = []

            for i in range(0, len(rf[0])):
                try:
                    flat_numbers = [
                        num[i] for num in rf
                        if num is not None and num[i] is not None
                    ]
                    if flat_numbers:
                        data_list.append(flat_numbers)
                    # coeffs = trendline(flat_numbers)
                    # res = judge_slope(coeffs,
                    #                   flat_numbers,
                    #                   degree=0.1,
                    #                   shake=100)

                    # # res = cox_stuart(flat_numbers, False)
                    # res_list.append(res)
                except Exception as e:
                    print(e)
                    # res_list.append(-2)
            time_list = data_list[-1]
            res_list, pre_list = get_prediction(time_list=time_list,
                                                data_list=data_list[:-1],
                                                plus_time=plus_time,
                                                debug=False)
            if rf[0][3] and rf[0][6]:
                result.append(f'{realname_list[index]}&')
                for index, name in enumerate(name_1zhan):
                    result.append(
                        f'{name}:{rf[0][index]}@{res_list[index]}@{pre_list[index]}'
                    )
                if rf[0][9] and rf[0][10]:
                    result.append(
                        f'lcU(V):{rf[0][9]}@{res_list[9]}@{pre_list[9]}')
                    result.append(
                        f'lcI(A):{rf[0][10]}@{res_list[10]}@{pre_list[10]}')
    c.close()
    conn.close()
    # print(','.join(result))
    return ' '.join(result)


def get_res_pre_lists(tagtxt, tag_index, rf, time_list, plus_time):
    flat_numbers = [num[tag_index] for num in rf]
    res_list, pre_list = get_prediction(time_list=time_list,
                                        data_list=[flat_numbers],
                                        plus_time=plus_time,
                                        debug=False)
    return f'{tagtxt}:{rf[0][tag_index]}@{res_list[0]}@{pre_list[0]}\n'


def getgq_1zhan_db(past_time, plus_time):
    '''
    数据库传送的数据获取，非监控画面
    '''
    result = []
    warn_list = []
    # result_pre = []
    conn = sqlite3.connect(db1_path)
    c = conn.cursor()
    sqltxt = "SELECT time FROM data ORDER BY time DESC LIMIT 1"
    rs = c.execute(sqltxt)
    rf = rs.fetchone()
    now_time = rf[0]
    result.append(
        time.strftime(r"%y年%m月%d日%H时%M分%S秒", time.localtime(now_time)) + "\n")
    start_time = now_time - past_time
    # 机组JZ1,2,3,4
    jz_list = ['JZ1', 'JZ2', 'JZ3', 'JZ4']
    # 数据库列名偏移量
    offset_index = 2
    # title = []
    for index, jz in enumerate(jz_list):
        # sqltxt = 'SELECT "6" FROM data WHERE name=? ORDER BY time DESC limit 1'
        # c.execute(sqltxt, (jz, ))
        # run = c.fetchone()
        # print(run)
        # if run:
        #     if int(run[0]) == 0:
        #         continue
        # title.append(str(index + 1))
        result.append(f'{index+1}号机组&')
        sqltxt = f"SELECT * FROM data WHERE time BETWEEN {start_time} AND {now_time} AND name='{jz}' ORDER BY time DESC"
        rs = c.execute(sqltxt)
        rf = rs.fetchall()
        time_list = []
        for i in range(0, len(rf)):
            time_list.append(rf[i][2])
        # 这里可以优化
        for tagtxt in jz_column_json.keys():
            # tagtxt = "Uab"
            result.append(
                get_res_pre_lists(tagtxt=tagtxt + jz_column_json[tagtxt][0],
                                  tag_index=int(jz_column_json[tagtxt][1]) +
                                  offset_index,
                                  rf=rf,
                                  time_list=time_list,
                                  plus_time=plus_time))

    # 变压器 ZJX
    result.append(f'变压器&')
    sqltxt = f"SELECT * FROM data WHERE time BETWEEN {start_time} AND {now_time} AND name='ZJX' ORDER BY time DESC"
    rs = c.execute(sqltxt)
    rf = rs.fetchall()
    time_list = []
    for i in range(0, len(rf)):
        time_list.append(rf[i][2])
    for tagtxt in zjx_column_json.keys():
        result.append(
            get_res_pre_lists(tagtxt=tagtxt + zjx_column_json[tagtxt][0],
                              tag_index=int(zjx_column_json[tagtxt][1]) +
                              offset_index,
                              rf=rf,
                              time_list=time_list,
                              plus_time=plus_time))
    # 供水相关 SHUI
    result.append(f'供水系统&')
    sqltxt = f"SELECT * FROM data WHERE time BETWEEN {start_time} AND {now_time} AND name='SHUI' ORDER BY time DESC"
    rs = c.execute(sqltxt)
    rf = rs.fetchall()
    time_list = []
    for i in range(0, len(rf)):
        time_list.append(rf[i][2])
    for tagtxt in shui_column_json.keys():
        # tagtxt = "Uab"
        result.append(
            get_res_pre_lists(tagtxt=tagtxt + shui_column_json[tagtxt][0],
                              tag_index=int(shui_column_json[tagtxt][1]) +
                              offset_index,
                              rf=rf,
                              time_list=time_list,
                              plus_time=plus_time))
    # 水位相关 JZ0
    insert_index = len(result)

    sqltxt = f"SELECT * FROM data WHERE time BETWEEN {start_time} AND {now_time} AND name='JZ0' ORDER BY time DESC"
    rs = c.execute(sqltxt)
    rf = rs.fetchall()
    time_list = []
    for i in range(0, len(rf)):
        time_list.append(rf[i][2])
    xysw0 = rf[0][5]
    for tagtxt in sq_column_json.keys():
        tag_index = int(sq_column_json[tagtxt][1]) + offset_index
        result.append(
            get_res_pre_lists(tagtxt=tagtxt + sq_column_json[tagtxt][0],
                              tag_index=tag_index,
                              rf=rf,
                              time_list=time_list,
                              plus_time=plus_time))
    conn.close()
    sysw, xysw1, swtime = getsw_sw("刘老涧抽水站")
    swtime = time.strftime(
        r"%m.%d..%H.00",
        time.localtime(
            int(time.mktime(time.strptime(swtime, '%Y-%m-%d %H:%M:%S')))))
    result.append(f'站-上游水位(m):@{sysw}@3@{swtime}\n')
    result.append(f'站-下游水位(m):@{xysw1}@3@{swtime}\n')
    sysw, xysw2, swtime = getsw_sw("刘老涧闸")
    swtime = time.strftime(
        r"%m.%d..%H.00",
        time.localtime(
            int(time.mktime(time.strptime(swtime, '%Y-%m-%d %H:%M:%S')))))
    result.append(f'闸-上游水位(m):@{sysw}@3@{swtime}\n')
    result.append(f'闸-下游水位(m):@{xysw2}@3@{swtime}\n')
    sysw, xysw3, swtime = getsw_sw("刘涧新闸")
    swtime = time.strftime(
        r"%m.%d..%H.00",
        time.localtime(
            int(time.mktime(time.strptime(swtime, '%Y-%m-%d %H:%M:%S')))))
    result.append(f'新闸-上游水位(m):@{sysw}@3@{swtime}\n')
    result.append(f'新闸-下游水位(m):@{xysw3}@3@{swtime}\n')
    delta_xysw1 = 0 if xysw2 - xysw0 < 0 else xysw2 - xysw0
    delta_xysw2 = 0 if xysw3 - xysw0 < 0 else xysw3 - xysw0
    # 检查是否有报警
    datatype = "水位差"
    warn1 = checkdata.check_data(datatype, delta_xysw1)
    warn1 = warn1 if type(warn1) == int else warn1[1]
    warn2 = checkdata.check_data(datatype, delta_xysw2)
    warn2 = warn2 if type(warn2) == int else warn2[1]
    warn = warn1 if warn1 > warn2 else warn2
    warn_list.append({
        datatype: warn,
    } if warn else warn)
    result.insert(insert_index,
                  f'水情Δ{round(delta_xysw1,2)}Δ{round(delta_xysw2,2)}&')
    result = ' '.join(result)
    return result, warn_list


def getgq_2zhan(past_time, plus_time):
    '''
    获取一站工情
    past_time: 过去时间 小时
    plus_time: 未来时间 分钟
    :return: 返回给微信群用的字符串
    '''
    result = []
    s = time.time()
    conn = sqlite3.connect(db2_path)
    cur = conn.cursor()
    # 获取表的列名
    # cur.execute("SELECT * FROM {}".format('dqcs'))
    # col_name_list = [tuple[0] for tuple in cur.description]
    # print(col_name_list)
    sqltxt = "SELECT time FROM qzcs ORDER BY time DESC LIMIT 1"
    rs = cur.execute(sqltxt)
    rf = rs.fetchone()
    now_time = rf[0]
    result.append(
        time.strftime(r"%y年%m月%d日%H时%M分%S秒", time.localtime(now_time)) + "\n")
    start_time = now_time - past_time
    sqltxt = f"SELECT sysw,sqsw,shsw,yc,ldsw,zbwd1,zbwd2,yxts,zll,time FROM qzcs WHERE time BETWEEN {start_time} AND {now_time} ORDER BY time DESC"
    rs = cur.execute(sqltxt)
    rf = rs.fetchall()
    data_list = []
    if rf:
        res_list = []
        for i in range(0, len(rf[0])):
            try:
                flat_numbers = [
                    num[i] for num in rf
                    if num is not None and num[i] is not None
                ]
                if flat_numbers:
                    data_list.append(flat_numbers)
                # data_list.append(flat_numbers)
                # res = cox_stuart(flat_numbers, False)

                # coeffs = trendline(flat_numbers)
                # res = judge_slope(coeffs, flat_numbers, degree=0.01, shake=1)
                # res_list.append(res)
            except Exception as e:
                print(e)
        time_list = data_list[-1]
        res_list, pre_list = get_prediction(time_list=time_list,
                                            data_list=data_list[:-1],
                                            plus_time=plus_time,
                                            debug=False)
        result.append("水情&")
        for index, n in enumerate(name_2zhan1):
            result.append(
                f'{n}:{rf[0][index]}@{res_list[index]}@{pre_list[index]}')
        # result.append(f'栅前(m):{rf[0][1]}')
        # result.append(f'栅后(m):{rf[0][2]}')
        # result.append(f'扬程(m):{rf[0][3]}')
        # result.append(f'廊道(m):{rf[0][4]}')
        # result.append(f'主变温度1(℃):{rf[0][5]}')
        # result.append(f'主变温度2(℃):{rf[0][6]}')
        # result.append(f'运行台数(台):{rf[0][7]}')
        # result.append(f'总流量(m³):{rf[0][8]}\n')
        # result.append( f'平均流量：{rf[0][4]}')
    # print(result)
    print('水位', time.time() - s)
    s = time.time()
    for index, name in enumerate(ename_list2):
        sqltxt = f'''SELECT P,Uab,Ubc,Uca,Ia,Ic,Q,COS,LC1,LC2,name,time FROM dydlpqcos WHERE name="{name}" AND time BETWEEN {start_time} and {now_time} order by time desc'''
        rs = cur.execute(sqltxt)
        rf = rs.fetchall()
        data_list = []
        if rf:
            res_list = []
            for i in range(0, len(rf[0])):
                try:
                    flat_numbers = [(num[i]) for num in rf
                                    if num is not None and num[i] is not None]
                    # res = cox_stuart(flat_numbers, False)
                    # coeffs = trendline(flat_numbers)
                    # res = judge_slope(coeffs, flat_numbers, degree=0.1, shake=1)
                    # res_list.append(res)
                    if flat_numbers:
                        data_list.append(flat_numbers)
                except Exception as e:
                    print(e)
                    res_list.append('')
            time_list = data_list[-1]
            res_list, pre_list = get_prediction(time_list=time_list,
                                                data_list=data_list[:-2],
                                                plus_time=plus_time,
                                                debug=False)
            result.append(f'{realname_list2[rf[0][-2]]}&')
            # sql_wd_txt = f'''SELECT * FROM wd WHERE name="{name}" AND time BETWEEN {start_time} and {now_time} order by time desc'''
            # rs_wd = cur.execute(sql_wd_txt)
            # rf_wd = rs_wd.fetchall()
            # data_wd_list = []
            # res_wd_list = []
            # if rf_wd:
            #     for i in range(len(rf_wd[0])):
            #         try:
            #             flat_wd_numbers = [(num[i]) for num in rf_wd
            #                             if num is not None and num[i] is not None]
            #             if flat_numbers:
            #                 data_wd_list.append(flat_wd_numbers)
            #         except Exception as e:
            #             print(e)
            #             res_wd_list.append('')
            # res_wd_list, pre_wd_list = get_prediction(time_list=time_list,
            #                                     data_list=data_wd_list[:-2],
            #                                     plus_time=plus_time,
            #                                     debug=False)
            for index, n in enumerate(name_2zhan2):
                # print(f'{n}:{rf[0][index]}@{res_list[index]}')
                result.append(
                    f'{n}:{rf[0][index]}@{res_list[index]}@{pre_list[index]}')
                # result.append(
                #     f'{n}温度:{rf[1][index]}@{res_wd_list[index]}@{pre_wd_list[index]}')

                # else:
                #     result.append(f'cosφ:{r[9]}@{res_list[9]}\n')
            if rf[0][8]:
                # result.append(f'cosφ:{r[9]}@{res_list[8]}')
                result.append(f'lc1(A):{rf[0][8]}@{res_list[8]}@{pre_list[8]}')
                result.append(
                    f'lc2(V):{rf[0][9]}@{res_list[9]}@{pre_list[9]}\n')
            if rf[0][-2] in ['1', '2', '3', '4']:
                sqltxt = f"SELECT * FROM dqcs WHERE name={rf[0][-2]} ORDER BY id DESC LIMIT 1"
                rs1 = cur.execute(sqltxt)
                rf1 = rs1.fetchone()
                if rf1:
                    # rf1 = rf1[0]
                    # print(rf1)
                    for index, r in enumerate(rf1):
                        if dqcs_column_list[index]:
                            result.append(
                                f'{dqcs_column_list[index]}:{r}@{3}@0')
                    result.append(f'真空度:{rf1[3]}@{3}@0')
        print(name, time.time() - s)
        s = time.time()
    print(time.time() - s)
    cur.close()
    conn.close()
    return ' '.join(result)


def save_gongshui_1zhan_image(timerange=12):
    '''
    获取供水系统参数的图片
    return: 返回给小程序用的图片
    '''
    now_time = int(time.time())
    begin_time = int(time.time() - timerange * 60 * 60)
    # print(now_time, begin_time)
    # 获取数据
    members_list = ['time', 'mgwd', 'cswd1', 'cswd2', 'cswd3']
    members_name = ['时间', '母管温度', '出水温度1', '出水温度2', '出水温度3']
    conn = sqlite3.connect(db1_path)
    df = pd.read_sql_query(
        f"SELECT {','.join(members_list)} FROM gongshui WHERE time BETWEEN {begin_time} AND {now_time} ORDER BY id",
        conn)
    conn.close()
    # print(df)
    fig, ax = plt.subplots(figsize=(4, 6))
    if not isinstance(df['time'].dtype, pd.core.dtypes.dtypes.DatetimeTZDtype):
        df['time'] = pd.to_datetime(df['time'], unit='s',
                                    utc=True)  # 先转换为UTC的datetime
        df['time'] = df['time'].dt.tz_convert(
            'Asia/Shanghai')  # 然后转换到上海时区（或你需要的时区）
    start_time = df['time'].iloc[0]
    end_time = df['time'].iloc[-1]

    for index, member in enumerate(members_list[1:]):
        # 获取特定列的最大值和最小值
        max_value = df[member].max()
        max_index = df[member].idxmax()
        max_time = df['time'].iloc[max_index]
        min_value = df[member].min()
        min_index = df[member].idxmin()
        min_time = df['time'].iloc[min_index]
        line, = ax.plot(df['time'], df[member])
        curve_color = line.get_color()  # 获取曲线的颜色
        cur_value = df[member].iloc[-1]
        ax.annotate(
            text=f"{members_name[index+1]} {cur_value:.1f}℃",
            xy=(end_time, cur_value),
            xycoords='data',
            xytext=(end_time + pd.Timedelta(hours=1), cur_value),
            rotation=45,
            textcoords='data',
            fontsize=9,
            color=curve_color,
            # arrowprops=dict(facecolor='blue')
        )
        ax.annotate(text=f"Max:{max_value:.1f}℃",
                    xy=(max_time, max_value),
                    xycoords='data',
                    xytext=(max_time, max_value + 0.0),
                    textcoords='data',
                    fontsize=9,
                    color=curve_color,
                    arrowprops=dict(facecolor=curve_color))
    # 手动指定图例项
    # plt.legend(['曲线1', '曲线2'])
    plt.xticks(rotation=45)
    ax.set_title(f"冷水机组{timerange}小时水温趋势")
    # ax.set_xlabel('时间')
    ax.legend(members_name[1:])
    # 调整子图间距和图表边缘空白
    # left, bottom, right, top 分别表示图表区左侧、底部、右侧、顶部边缘距离图形窗口边缘的比例
    # wspace 和 hspace 分别是宽度和高度方向上的子图间距
    fig.subplots_adjust(
        # left=0.1,
        bottom=0.1,
        right=0.8,
        top=0.95,
        # wspace=0.2,
        # hspace=0.2
    )
    fig.savefig(f'./image/#1_gs.png', dpi=80)
    plt.close(fig)  # 关闭图形以释放资源


def getnydh_2zhan_8image():
    '''
    获取二站8时工情的图片
    return: 返回给小程序用的图片
    '''
    nengyuandanhao_head = ('时间段', '取值时间', '上游水位', '下游水位', '水位差', '开机台数', '流量',
                           '主机功率', '全站功率', '主机能源单耗', '辅机能源单耗', '全站能源单耗')
    shuiqing_head = ('时间', '下游水位', '上游水位', '开机台数', '台时', '瞬时流量', '日均流量',
                     '累计流量')
    result = []
    conn = sqlite3.connect(db2_path)
    cur = conn.cursor()
    # 获取当前时间
    end_time = time.strftime("%Y-%m-%d 08:00:00",
                             time.localtime(int(time.time())))
    timeArray = time.strptime(end_time, "%Y-%m-%d %H:%M:%S")
    end_timestamp = int(time.mktime(timeArray))
    start_timestamp = end_timestamp - 86400
    start_time = time.strftime("%Y-%m-%d 08:00:00",
                               time.localtime(start_timestamp))
    # print(start_time, end_time)
    # 需要查询的时间list
    target_time_list = []
    for t in range(start_timestamp, end_timestamp + 7200, 7200):
        # print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(t)))
        target_time_list.append(t)
    # print(target_time_list)
    # 查询数据
    # sqltxt = f"SELECT sysw,shsw,time FROM qzcs WHERE time between {start_timestamp} AND {end_timestamp}"
    sqltxt = f"SELECT time FROM qzcs WHERE time BETWEEN {start_timestamp} AND {end_timestamp}"
    # print(sqltxt)
    result = cur.execute(sqltxt)
    rf = result.fetchall()
    # 数据库中存在的范围内全部时间list
    real_time_list = []
    if rf:
        for r in rf:
            if r:
                real_time_list.append(r[0])
                # print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(r[0])))
    # 所需查询的时间与数据库中存在的范围内全部时间最近的时间list
    nearest_time_list = []
    temp_list = []
    for timestamp in target_time_list:
        diffs = [abs(int(timestamp) - num) for num in real_time_list]
        for real_time in real_time_list:
            temp_list.append(
                f"""{time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(timestamp))}-{time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(real_time))}={int(timestamp) - int(real_time)}\n"""
            )
            # temp_list(abs(int(timestamp) - real_time))
        # with open('./data/temp.txt', 'a') as f:
        #     f.writelines(temp_list)
        # 找出时间差值最小的索引
        min_diff_index = diffs.index(min(diffs))
        # 获取具有最小差值的列表元素
        nearest_number = real_time_list[min_diff_index]
        # print(nearest_number)
        nearest_time_list.append(nearest_number)
        # print('目标时间:', time.strftime("%Y-%m-%d %H:%M:%S",
        #                              time.localtime(timestamp)), '实际时间:',
        #       time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(nearest_number)))

    # sqltxt = f"SELECT time FROM qzcs WHERE time between {start_timestamp} AND {end_timestamp}"
    # all_results = []
    sum_ts = 0  # 累计台时
    temp_sum_ll = 0  # 用于计算临时流量
    nengyuandanhao_table = []  # 能源单耗表
    nengyuandanhao_table.append(nengyuandanhao_head)
    real_time_list = []
    shuiqing_table = []
    shuiqing_table.append(shuiqing_head)
    print("能源单耗表")
    j = 0
    for i, nearest_time in enumerate(nearest_time_list):
        sqltxt1 = f"SELECT name,jsll,time FROM dqcs WHERE time={nearest_time}"
        cur.execute(sqltxt1)
        results1 = cur.fetchall()
        valid_results = []
        sum_single_ll = 0
        sum_Power = 0
        quanzhan_Power = 0
        shuiwei_results = []
        for r in (results1):
            if int(r[1]) != 0:
                valid_results.append((
                    r[0],
                    r[1],
                    time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(r[2])),
                ))
                sum_single_ll += float(r[1])
                sqltxt2 = f"SELECT P FROM dydlpqcos WHERE time>={nearest_time} AND name={r[0]} order by time limit 1"
                cur.execute(sqltxt2)
                results2 = cur.fetchall()
                if results2:
                    sum_Power += float(results2[0][0])
        sqltxt3 = f"SELECT P FROM dydlpqcos WHERE time>={nearest_time} AND name='zhbg' AND P<>0 order by time limit 1"
        cur.execute(sqltxt3)
        results3 = cur.fetchall()
        if results3:
            quanzhan_Power = results3[0][0]
            quanzhan_Power = float(quanzhan_Power)
            # if quanzhan_Power == 0:
            #     quanzhan_Power = sum_Power
        sqltxt4 = f"SELECT sysw,shsw FROM qzcs WHERE time = {nearest_time}"
        # print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(nearest_time)))
        cur.execute(sqltxt4)
        results4 = cur.fetchall()
        shuiwei_results.extend(results4)
        # print(shuiwei_results)
        if valid_results and i > 0:
            real_time_list.append(
                time.strftime("%Y-%m-%d %H:%M:%S",
                              time.localtime(nearest_time)),
                #   nearest_time
            )
            kjts = len(valid_results)  # 开机台时
            sysw = float(shuiwei_results[0][0])  #上游水位
            xysw = float(shuiwei_results[0][1])  #下游水位
            swch = round(sysw - xysw, 2)  #水位差
            sum_Power = round(sum_Power, 1)  #总功率
            sum_single_ll = round(sum_single_ll, 1)  #流量
            zjnydh = round(sum_Power / (3.6 * swch * sum_single_ll),
                           2)  #主机能源单耗
            qznydh = round(quanzhan_Power / (3.6 * swch * sum_single_ll),
                           2)  #全站能源单耗
            fjnydh = round(qznydh - zjnydh, 2)  #辅机能源单耗
            sum_ts += kjts * 2  #累计台时
            temp_sum_ll += sum_single_ll
            nengyuandanhao_table.append((
                f'{time.strftime("%H:%M",time.localtime(target_time_list[i-1]))}~{time.strftime("%H:%M",time.localtime(target_time_list[i]))}',
                real_time_list[j][-8:], sysw, xysw, swch, kjts, sum_single_ll,
                sum_Power, quanzhan_Power, zjnydh, fjnydh, qznydh))
            j += 1
    if temp_sum_ll:
        aver_ll = round(temp_sum_ll / 12, 1)  # 日均流量 24小时
        sum_ll = int(round(aver_ll * 8.64, 0))  # 当日水量
        # shuiqing_table = f"下游水位:{xysw} 上游水位:{sysw} 开机台数:{kjts} 累计台时:{sum_ts} 瞬时流量:{sum_single_ll} 日均流量:{aver_ll} 当日水量:{sum_ll}"
        shuiqing_table.append(
            (time.strftime("%m月%d日08时", time.localtime(int(time.time()))),
             xysw, sysw, kjts, sum_ts, sum_single_ll, aver_ll, sum_ll))
        print('取值实际时间\n', '\n'.join(real_time_list))
        print(nengyuandanhao_table)
        print("水情表")
        new_array = []
        # print(len(shuiqing_table[0]))
        for i in range(0, len(shuiqing_table[0]) - 1, 4):
            # print(i, temp_array[0][i])
            new_array.append(
                (shuiqing_table[0][i], shuiqing_table[0][i + 1],
                 shuiqing_table[0][i + 2], shuiqing_table[0][i + 3]))
            new_array.append(
                (shuiqing_table[1][i], shuiqing_table[1][i + 1],
                 shuiqing_table[1][i + 2], shuiqing_table[1][i + 3]))
        shuiqing_table = new_array
        print(shuiqing_table)
        conn.close()
        image1 = getnydhbpic(nengyuandanhao_table, '2znydhb', angle=-90)
        image2 = getnydhbpic(shuiqing_table, '2zsq')

        return image1, image2
    else:
        return None, None


def get_detail_from_db(station='0', n='', p=''):
    '''
    从数据库中获取数据
    :param station:0,1 一站,二站
    :param n:ename,name
    :param p:列名
    :return:
    '''
    result = ''
    db_path = ''
    if int(station) == 0:
        db_path = db1_path
        name = 'name'
        table = 'data'
        # table = 'dianyadianliu'
        table_list = ename_list1
        name_list = realname_list1
        if p in shui_column_json.keys():
            p = shui_column_json[p][1]
        elif p in jz_column_json.keys():
            p = jz_column_json[p][1]
        elif p in sq_column_json.keys():
            p = sq_column_json[p][1]
        elif p in zjx_column_json.keys():
            p = zjx_column_json[p][1]
        # col_list = column_list1
        # col_name_list = column_name_list1
    elif int(station) == 1:
        db_path = db2_path
        name = 'name'
        table = 'dydlpqcos'
        table_list = ename_list2
        name_list = realname_list_2
    if db_path:
        now_time = int(time.time())
        begin_time = int(time.time() - 48 * 60 * 60)
        conn = sqlite3.connect(db_path)
        cur = conn.cursor()
        if f'{n} ' in name_list:
            n = table_list[name_list.index(f'{n} ')]
        # if p in col_name_list:
        #     p = col_list[col_name_list.index(p)]

        sqltxt = f'select "{p}",time from {table} where {name}="{n}" and time between {begin_time} and {now_time} order by time desc'
        # print(sqltxt)
        cur.execute(sqltxt)
        result = cur.fetchall()
        cur.close()
        conn.close()
    else:
        result = 'error station'
    # print(result)
    return result


def time_to_timestamp(hour=00, minute=00, second=00, step=0):
    '''
    时间戳转换
    :param hour:小时
    :param minute:分钟
    :param second:秒
    :param step:步长
    :return:目标时间戳
    '''
    next_time = 86400 * step
    now_time = int(time.time()) + next_time
    time_str = time.strftime(f"%Y-%m-%d {hour}:{minute}:{second}",
                             time.localtime(now_time))
    date_object = datetime.strptime(time_str, "%Y-%m-%d %H:%M:%S")
    timestamp = int(date_object.timestamp())
    return timestamp


def get_camera(get=True):
    '''
    获取摄像头图片
    '''
    if get:
        with open('./filedata/camera.txt', 'r', encoding='utf-8') as f:
            camera_list = f.readlines()
        with open('./filedata/recent.txt', 'r', encoding='utf-8') as f:
            recent_dir = f.read()
        return camera_list, recent_dir
    # print(os.walk('./filedata'))
    return


def get_camera_path(camera_name, camera_time=None):
    conn = sqlite3.connect('./data/camera.db')
    cur = conn.cursor()
    if camera_time:
        sqltxt = f"SELECT time FROM camera1 WHERE name='{camera_name}' AND time={camera_time}"
    else:
        sqltxt = f"SELECT time FROM camera1 WHERE name='{camera_name}' ORDER  BY time DESC LIMIT 1"
    cur.execute(sqltxt)
    file_path = cur.fetchall()
    conn.close()
    file_time = file_path[0][0]
    file_path = time.strftime("%Y/%m/%d/%H", time.localtime(file_time))
    # \filedata\2025\05\28\17
    file_path = rf'./filedata/{file_path}/{file_time}_zk_{camera_name}.jpg'
    if os.path.exists(file_path):
        with open(file_path, "rb") as buffer:
            img_bytes = buffer.read()
            return img_bytes
    return None


if __name__ == '__main__':
    old = os.listdir('./filedata/2025/05/22/23')
    print(old)
    old = sorted(old, reverse=True)
    temp = ' '.join(old)
    result = re.findall(r'(.*?_zk_4#.jpg)', temp)

    print(result[0])
    # get_camera()
    # getwd_zhan_image(0)
    # getwd_zhan_image(0)
    # save_gongshui_1zhan_image(24)
    # getnydh_2zhan_8image()
    # print(check_status(1))
    # print(check_status(2))
    # getgq_1zhan_image()
    # getgq_1zhan_image()
    # print(getgq_2zhan())
    # print(getgq_1zhan())
    # (getgq_1zhan_db(past_time=60 * 60 * 3, plus_time=10 * 60))
    # sysw, xysw, swtime = getsw_sw('刘老涧抽水站')
    print()
    # get_main_image('刘老涧抽水二站', 2)
    # get_main_image('刘老涧抽水站', 1)
    # save_wd_1zhan_image()
